package com.gem.controller;

import com.alibaba.druid.support.json.JSONUtils;
import com.alibaba.druid.util.StringUtils;
import com.gem.domain.TrainingOrder;
import com.gem.domain.User;
import com.gem.service.TrainingOrderService;
import com.gem.service.UserService;
import com.gem.util.ExcelUtil;
import com.gem.util.JSONResultUtil;
import com.gem.util.MessageUtil;
import com.gem.util.PageUtil;
import com.github.pagehelper.PageInfo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * @author Bravea❤ ·勇
 * Description: 实训订单controller
 * create
 * Modified By:
 */
@Controller
@RequestMapping("/training")
public class TrainingOrderController {

    @Autowired
    private JSONResultUtil result;

    @Autowired
    private UserService userService;

    @Autowired
    private TrainingOrderService trainingOrderService;

    @RequestMapping("/trainingOrderList")
    public String trainingOrderList(Model model,HttpSession session,
                                    @RequestParam(value = "currentPage",defaultValue = "1") Integer currentPage,
                                    @RequestParam(value = "pageSize",defaultValue = "5") Integer pageSize){
        User user = (User) session.getAttribute("user");
        if (user == null) {
            MessageUtil.returnInfoPage("3", "/starter", "用户未登录", model);
            return "message";
        }
        PageInfo<TrainingOrder> pageBean = null;
        if(user.getDepartment() != null && user.getDepartment().equals("市场") && user.getAuthority() == 1){
            pageBean = trainingOrderService.selectTrainingOrderByPageNoOther(currentPage,pageSize);
        }else if (user.getDepartment() != null && user.getDepartment().equals("财务") && user.getAuthority() == 1){
            pageBean = trainingOrderService.selectTrainingOrderByPage(currentPage,pageSize);
        }else {
            pageBean = trainingOrderService.selectMyTrainingOrderByPage(currentPage,pageSize,user.getPersonname());
        }
        if (currentPage > pageBean.getPages()){
            currentPage = pageBean.getPages();//最后一页被删光了回到上一页
            return "redirect:/training/trainingOrderList?currentPage=" + currentPage + "&pageSize=" + pageSize;
        }
        if(pageBean != null && pageBean.getList().size() > 0 && pageBean.getPages() > 0 && pageBean.getTotal() > 0){
            List<Integer> pageValue = PageUtil.getPageValue(pageBean.getPages(),currentPage);
            model.addAttribute("pageValue",pageValue);
        }else {
            model.addAttribute("info","没有订单数据请先添加！");
        }
        model.addAttribute("pageBean", pageBean);
        return "Training/TrainingOrder";
    }

    @RequestMapping("/trainingOrderModifyUI")
    public String trainingOrderModifyUI(String trainingOrderId,Model model,String searchUrl,
                                        @RequestParam(value = "currentPage",defaultValue = "1") Integer currentPage,
                                        @RequestParam(value = "pageSize",defaultValue = "5") Integer pageSize){
        if(trainingOrderId == null || trainingOrderId.trim().equals("")){
            MessageUtil.returnInfoPage("3","/training/trainingOrderList?currentPage="+currentPage+"&pageSize="+pageSize,"传递参数为空",model);
            return "message";
        }
        TrainingOrder trains = trainingOrderService.selectTrainingOrderById(trainingOrderId);
        if (trains == null){
            MessageUtil.returnInfoPage("3","/training/trainingOrderList?currentPage="+currentPage+"&pageSize="+pageSize,"没有该订单",model);
            return "message";
        }
        List<User> mmembers = userService.findMmembersWithNull();
        List<User> users = userService.findAllUser();
        if(mmembers==null || mmembers.size() <= 0 || users == null || users.size() <= 0){
            if(searchUrl != null && !searchUrl.trim().equals("")){
                MessageUtil.returnInfoPage("3",searchUrl+"&currentPage="+currentPage+"&pageSize="+pageSize,"没有市场人员，请先添加",model);
            }else {
                MessageUtil.returnInfoPage("3","/training/trainingOrderList?currentPage="+currentPage+"&pageSize="+pageSize,"没有市场人员，请先添加",model);
            }
            return "message";
        }
        model.addAttribute("users",users);
        model.addAttribute("mmembers",mmembers);
        model.addAttribute("searchUrl",searchUrl);
        model.addAttribute("currentPage",currentPage);
        model.addAttribute("pageSize",pageSize);
        model.addAttribute("trains",trains);
        return "Training/TrainingOrder_update";
    }

    @RequestMapping("/trainingOrderModify")
    public String trainingOrderModify(TrainingOrder tor,Model model,String searchUrl,
                                      @RequestParam(value = "currentPage",defaultValue = "1") Integer currentPage,
                                      @RequestParam(value = "pageSize",defaultValue = "5") Integer pageSize){
        boolean isModifySuccess = trainingOrderService.modifyTrainingOrderById(tor);
        if(isModifySuccess){
            if(searchUrl != null && !searchUrl.trim().equals("")){
                MessageUtil.returnInfoPage("3",searchUrl+"&currentPage="+currentPage+"&pageSize="+pageSize,"修改成功！",model);
            }else {
                MessageUtil.returnInfoPage("3", "/training/trainingOrderList?currentPage=" + currentPage + "&pageSize=" + pageSize, "修改成功！", model);
            }
        }else {
            if(searchUrl != null && !searchUrl.trim().equals("")){
                MessageUtil.returnInfoPage("3",searchUrl+"&currentPage="+currentPage+"&pageSize="+pageSize,"修改未成功，请检查！",model);
            }else {
                MessageUtil.returnInfoPage("3", "/training/trainingOrderList?currentPage=" + currentPage + "&pageSize=" + pageSize, "修改未成功，请检查！", model);
            }
        }
        return "message";
    }

    @RequestMapping("/trainingOrderAddUI")
    public String trainingOrderAddUI(@RequestParam(value = "currentPage",defaultValue = "1") Integer currentPage,String searchUrl,
                                     @RequestParam(value = "pageSize",defaultValue = "5") Integer pageSize,Model model){
        List<User> mmembers = userService.findMmembersWithNull();
        List<User> users = userService.findAllUser();
        if(mmembers == null || mmembers.size() <= 0 || users == null || users.size() <= 0){
            if(searchUrl != null && !searchUrl.trim().equals("")){
                MessageUtil.returnInfoPage("3",searchUrl+"&currentPage="+currentPage+"&pageSize="+pageSize,"没有员工，请先添加",model);
            }else {
                MessageUtil.returnInfoPage("3","/training/trainingOrderList?currentPage="+currentPage+"&pageSize="+pageSize,"没有员工，请先添加",model);
            }
            return "message";
        }
        model.addAttribute("users",users);
        model.addAttribute("mmembers",mmembers);
        model.addAttribute("currentPage",currentPage);
        model.addAttribute("pageSize",pageSize);
        model.addAttribute("searchUrl",searchUrl);
        return "Training/TrainingOrder_insert";
    }

    @RequestMapping("/trainingOrderAdd")
    public String trainingOrderAdd(TrainingOrder t,Model model,String searchUrl,
                                   @RequestParam(value = "currentPage",defaultValue = "1") Integer currentPage,
                                   @RequestParam(value = "pageSize",defaultValue = "5") Integer pageSize){
        boolean isAddSuccess = trainingOrderService.addTrainingOrder(t);
        if(isAddSuccess){
            if(searchUrl != null && !searchUrl.trim().equals("")){
                MessageUtil.returnInfoPage("3",searchUrl+"&currentPage="+currentPage+"&pageSize="+pageSize,"添加成功！",model);
            }else {
                MessageUtil.returnInfoPage("3","/training/trainingOrderList?currentPage="+currentPage+"&pageSize="+pageSize,"添加成功！",model);
            }
        }else {
            if(searchUrl !=null && !searchUrl.trim().equals("")){
                MessageUtil.returnInfoPage("3",searchUrl+"&currentPage="+currentPage+"&pageSize="+pageSize,"添加失败，请检查！",model);
            }else {
                MessageUtil.returnInfoPage("3","/training/trainingOrderList?currentPage="+currentPage+"&pageSize="+pageSize,"添加失败，请检查！",model);
            }
        }
        return "message";
    }

    @RequestMapping("/trainingOrderSearch")
    public String trainingOrderSearch(Model model,String trainingOrderPayDate,TrainingOrder o,HttpSession session,
                                      @RequestParam(value = "currentPage",defaultValue = "1") Integer currentPage,
                                      @RequestParam(value = "pageSize",defaultValue = "5") Integer pageSize){
        User user = (User) session.getAttribute("user");
        if (user == null) {
            MessageUtil.returnInfoPage("3", "/starter", "用户未登录", model);
            return "message";
        }
        if (trainingOrderPayDate == null){
            trainingOrderPayDate = "";
        }
        if (o.getTrainingOrderIDNumber() == null){
            o.setTrainingOrderIDNumber("");
        }
        if (o.getTrainingOrderName() == null){
            o.setTrainingOrderName("");
        }
        PageInfo<TrainingOrder> pageBean = null;
        if(user.getDepartment() != null && user.getDepartment().equals("市场") && user.getAuthority() == 1){
            pageBean = trainingOrderService.searchTrainingOrderByPageNoOther(currentPage,pageSize,o.getTrainingOrderName(),o.getTrainingOrderIDNumber(),o.getTrainingOrderPayDate());
        }else if (user.getDepartment() != null && user.getDepartment().equals("财务") && user.getAuthority() == 1){
            pageBean = trainingOrderService.searchTrainingOrderByPage(currentPage,pageSize,o.getTrainingOrderName(),o.getTrainingOrderIDNumber(),o.getTrainingOrderPayDate());
        }else {
            pageBean = trainingOrderService.searchMyTrainingOrderByPage(currentPage,pageSize,o.getTrainingOrderName(),o.getTrainingOrderIDNumber(),o.getTrainingOrderPayDate(),user.getPersonname());
        }
        if (currentPage > pageBean.getPages()){
            currentPage = pageBean.getPages();//最后一页被删光了回到上一页
            if(user.getDepartment() != null && user.getDepartment().equals("市场") && user.getAuthority() == 1){
                pageBean = trainingOrderService.searchTrainingOrderByPageNoOther(currentPage,pageSize,o.getTrainingOrderName(),o.getTrainingOrderIDNumber(),o.getTrainingOrderPayDate());
            }else if (user.getDepartment() != null && user.getDepartment().equals("财务") && user.getAuthority() == 1){
                pageBean = trainingOrderService.searchTrainingOrderByPage(currentPage,pageSize,o.getTrainingOrderName(),o.getTrainingOrderIDNumber(),o.getTrainingOrderPayDate());
            }else {
                pageBean = trainingOrderService.searchMyTrainingOrderByPage(currentPage,pageSize,o.getTrainingOrderName(),o.getTrainingOrderIDNumber(),o.getTrainingOrderPayDate(),user.getPersonname());
            }
        }
        if(pageBean != null && pageBean.getList().size() > 0 && pageBean.getPages() > 0 && pageBean.getTotal() > 0){
            List<Integer> pageValue = PageUtil.getPageValue(pageBean.getPages(),currentPage);
            model.addAttribute("pageValue",pageValue);
        }else {
            model.addAttribute("info","没有订单数据请先添加！");
        }
        model.addAttribute("pageBean",pageBean);
        model.addAttribute("trainingOrderName",o.getTrainingOrderName());
        model.addAttribute("trainingOrderIDNumber",o.getTrainingOrderIDNumber());
        model.addAttribute("trainingOrderPayDate",trainingOrderPayDate);
        model.addAttribute("searchUrl","/training/trainingOrderSearch?trainingOrderName="+o.getTrainingOrderName()+"&trainingOrderIDNumber="+o.getTrainingOrderIDNumber()+"&trainingOrderPayDate="+trainingOrderPayDate);
        return "Training/TrainingOrder";
    }

    @RequestMapping(value = "/trainingOrderDelete",method = RequestMethod.POST)
    @ResponseBody
    public JSONResultUtil trainingOrderDelete(String[] checks){
        try {
            if (checks != null && checks.length>0) {
                if (trainingOrderService.deleteTrainingOrderById(checks)) {
                    result.setId(0);
                    result.setData("删除成功！");
                } else {
                    result.setId(-1);
                    result.setData("删除失败！");
                }
            } else {
                result.setId(-1);
                result.setData("没有提交编号参数哦！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.setId(-1);
            result.setData("服务器异常，请联系管理员哦！");
        }
        return result;
    }


    /**
     * 以下是POI部分
     */
    //导出excel模板
    @RequestMapping("/exportTemplate")
    public void exportExcelTemplate(HttpServletResponse response,HttpSession session){
        try {
            //创建excel工作簿对象//一个表格文件！
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建sheet对象//一个表单！
            HSSFSheet sheet = workbook.createSheet("Sheet1");

            //创建水平垂直居中的cellStyle单元格样式对象
            HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

            //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
            HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

            //模板第一行例子展示
            HSSFCellStyle cellStyle3 = ExcelUtil.getExamplStyle(workbook);

            // 单元格类型日期格式化
            HSSFCellStyle cellStyleDate = ExcelUtil.getDateRedStyle(workbook);

            //创建行对象
            HSSFRow row = null;

            // 将第一行的三个单元格给合并,模板固定
            //注意 java的参数是从0开始 即表示excel中的一行或者第一列  //Row行 Col列
            sheet = getRows(sheet, cellStyle, cellStyle2);
            User user = (User) session.getAttribute("user");
            String[] name = {user.getPersonname()};
            sheet = ExcelUtil.setHSSFValidation(sheet, name, 3, 10000, 1, 1);
            //在第四行给一行数据模板,根据对应表的对应字段填写
            row = sheet.createRow(3);
            row.createCell(0).setCellValue("张三");
            //第二列 实训基地
            row.createCell(1).setCellValue("苏州园区基地");
            //第三列 身份证号
            row.createCell(2).setCellValue("321002199604092110");
            //第四列 联系方式
            row.createCell(3).setCellValue("17864200082");
            //第五列 性别
            row.createCell(4).setCellValue("男");
            //第六列 实训院校
            row.createCell(5).setCellValue("清华大学");
            //第七列 学历
            row.createCell(6).setCellValue("本科");
            //第八列 缴费日期
            //优化 降低冗余
            HSSFCell cell = null;//主要用于表格里时间的格式化
            cell = row.createCell(7);
            cell.setCellStyle(cellStyleDate);
            cell.setCellValue(new Date());
            //第九列 缴费类型
            row.createCell(8).setCellValue("贷款");
            //第十列 院校补贴
            row.createCell(9, CellType.NUMERIC).setCellValue(1000);
            //第十一列 补贴到账
            row.createCell(10).setCellValue("是");
            //第十二列 住宿费
            row.createCell(11, CellType.NUMERIC).setCellValue(800);
            //第十三列 住宿押金
            row.createCell(12, CellType.NUMERIC).setCellValue(200);
            //第十四列 代办费
            row.createCell(13, CellType.NUMERIC).setCellValue(4000);
            //第十五列 折扣类型
            row.createCell(14).setCellValue("内部优惠");
            //第十六列 渠道专员
            row.createCell(15).setCellValue("市场员工1");
            //第十七列 咨询专员
            row.createCell(16).setCellValue("市场员工2");
            //第十七列
            row.createCell(17).setCellValue("备注");
            for (int i = 0; i < 18; i++) {
                if (i != 7) {
                    row.getCell(i).setCellStyle(cellStyle3);
                }
            }

            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=trainingOrder.xls");

            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //导出excel
    @RequestMapping("/export")
    public void exportExcel(HttpServletResponse response,HttpSession session) {
        try {
            User user = (User) session.getAttribute("user");
            //创建excel工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建sheet对象
            HSSFSheet sheet = workbook.createSheet("Sheet1");

            //创建水平垂直居中的cellStyle单元格样式对象
            HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

            //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
            HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

            //居中
            HSSFCellStyle cellStyle3 = ExcelUtil.getCenterStyle(workbook);

            // 单元格类型日期格式化
            HSSFCellStyle cellStyleDate = ExcelUtil.getDateStyle(workbook);

            //eg.
            /*HSSFCell cell2 = row1.createCell(2);
            cell2.setCellStyle(cellStyle2);
            cell2.setCellValue(new Date());*/

            //创建行对象
            HSSFRow row = null;
            sheet = getRows(sheet, cellStyle, cellStyle2);
            List<TrainingOrder> orderList =null;
            if (user.getDepartment() != null && user.getDepartment().equals("财务") && user.getAuthority() == 1){
                //调用service方法查询所有数据集合
                orderList = trainingOrderService.selectAllTrainingOrder();
            }else if (user.getDepartment() != null && user.getDepartment().equals("市场") && user.getAuthority() == 1){
                orderList = trainingOrderService.selectAllTrainingOrderNoOther();
            }else {
                orderList = new ArrayList<>();
            }

            //优化 降低冗余
            HSSFCell cell = null;
            TrainingOrder order = null;

            for (int i = 0; i < orderList.size(); i++) {
                row = sheet.createRow(i + 3);
                order = orderList.get(i);
                //第一列,姓名
                row.createCell(0).setCellValue(order.getTrainingOrderName());
          /*cell = row.createCell(0);
          cell.setCellStyle(cellStyleDate);//改单元格为日期样式
          cell.setCellValue(ds.getDsDate());//赋值*/
                //第二列 实训基地 数字类型 需要加一个数字类型指定参数
                row.createCell(1).setCellValue(order.getTrainingOrderTrainingBase());
                //第三列 身份证号
                row.createCell(2).setCellValue(order.getTrainingOrderIDNumber());
                //第四列 联系方式
                row.createCell(3).setCellValue(order.getTrainingOrderTel());
                //第五列 性别
                row.createCell(4).setCellValue(order.getTrainingOrderSex());
                //第六列 院校
                row.createCell(5).setCellValue(order.getTrainingOrderSchool());
                //第七列 学历
                row.createCell(6).setCellValue(order.getTrainingOrderEducation());
                //第八列 缴费日期
                cell = row.createCell(7);
                cell.setCellStyle(cellStyleDate);
                cell.setCellValue(order.getTrainingOrderPayDate());
                //第九列 缴费类型
                row.createCell(8).setCellValue(order.getTrainingOrderPayType());
                //第十列 院校补贴
                row.createCell(9, CellType.NUMERIC).setCellValue(order.getTrainingOrderSchoolSubsidy());
                //第十一列 补贴到账
                row.createCell(10).setCellValue(order.getTrainingOrderSubsidyState());
                //第十二列 住宿费
                row.createCell(11, CellType.NUMERIC).setCellValue(order.getTrainingOrderLiveFee());
                //第十三列 住宿押金
                row.createCell(12, CellType.NUMERIC).setCellValue(order.getTrainingOrderLiveFeeCase());
                //第十四列 代办费
                row.createCell(13, CellType.NUMERIC).setCellValue(order.getTrainingOrderAgencyFee());
                //第十五列 折扣类型
                row.createCell(14).setCellValue(order.getTrainingOrderDiscountType());
                //第十六列 渠道专员
                row.createCell(15).setCellValue(order.getChannelName());
                //第十七列 咨询专员
                row.createCell(16).setCellValue(order.getConsultName());
                //第十八列 备注
                row.createCell(17).setCellValue(order.getTrainingOrderRemarks());

                //设置垂直水平居中
                for (int j = 0; j < 18; j++) {
                    if(j!=7){
                        row.getCell(j).setCellStyle(cellStyle3);
                    }
                }
            }

            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=trainingOrder.xls");

            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //导出模板和导出数据抽出的共同方法
    private HSSFSheet getRows(HSSFSheet sheet, HSSFCellStyle cellStyle, HSSFCellStyle cellStyle2) {
        HSSFRow row;// 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 17));
        row = sheet.createRow(0);
        HSSFCell beginCell0 = row.createCell(0);
        beginCell0.setCellValue("实训订单表");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell0.setCellStyle(cellStyle);

        //第二行的单元格进行合并,语句提示
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 17));
        row = sheet.createRow(1);
        HSSFCell beginCell1 = row.createCell(0);
        beginCell1.setCellValue("请按照模板字段和单元格格式提交数据,日期类型请使用给定格式刷刷一下");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell1.setCellStyle(cellStyle);

        HSSFRow headRow = sheet.createRow(2);
        headRow.createCell(0).setCellValue("*姓名");
        headRow.createCell(1).setCellValue("*实训基地");
        headRow.createCell(2).setCellValue("身份证号");
        headRow.createCell(3).setCellValue("*联系方式");
        headRow.createCell(4).setCellValue("*性别");
        headRow.createCell(5).setCellValue("院校");
        headRow.createCell(6).setCellValue("学历");
        headRow.createCell(7).setCellValue("缴费日期");
        headRow.createCell(8).setCellValue("缴费类型");
        headRow.createCell(9).setCellValue("院校补贴");
        headRow.createCell(10).setCellValue("补贴到账");
        headRow.createCell(11).setCellValue("住宿费");
        headRow.createCell(12).setCellValue("住宿押金");
        headRow.createCell(13).setCellValue("代办费");
        headRow.createCell(14).setCellValue("折扣类型");
        headRow.createCell(15).setCellValue("*渠道专员");
        headRow.createCell(16).setCellValue("*咨询专员");
        headRow.createCell(17).setCellValue("备注");
        headRow.setHeightInPoints(25); // 设置行的高度
        for (int i = 0; i < 18; i++) {
            headRow.getCell(i).setCellStyle(cellStyle2);
        }

        //下拉列表的参数
        String[] trainBase = {"武汉园区基地","苏州园区基地"};
        String[] genderList = {"男", "女"};
        String[] payTypeList = {"贷款", "转账", "现金", "消费"};
        String[] alreadyList = {"是", "否"};
        String[] discountTypeList = {"合作院校", "内部优惠", "无优惠"};
        List<User> hrc = userService.findMmembersWithNull();
        String[] trainHrcs = new String[hrc.size()];
        int index = 0;
        for (User user : hrc) {
            trainHrcs[index] = user.getPersonname();
            index++;
        }
        List<User> allUser = userService.findAllUser();
        String[] allUsers = new String[allUser.size()];
        index = 0;
        for (User user : allUser) {
            allUsers[index] = user.getPersonname();
            index ++;
        }
        //设置当前工作表的什么地方需要下拉,需要自己对着表格设置位置,没有就不需要写
        sheet = ExcelUtil.setHSSFValidation(sheet, trainBase, 3, 10000, 1, 1);
        sheet = ExcelUtil.setHSSFValidation(sheet, genderList, 3, 10000, 4, 4);
        sheet = ExcelUtil.setHSSFValidation(sheet, payTypeList, 3, 10000, 8, 8);
        sheet = ExcelUtil.setHSSFValidation(sheet, alreadyList, 3, 10000, 10, 10);
        sheet = ExcelUtil.setHSSFValidation(sheet, discountTypeList, 3, 10000, 14, 14);
        sheet = ExcelUtil.setHSSFValidation(sheet, trainHrcs,3, 10000, 15, 15);//渠道专员限定市场的人
        sheet = ExcelUtil.setHSSFValidation(sheet, allUsers, 3, 10000, 16, 16);
        //设置所有列列宽
        for (int i = 0; i < 18; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        } // 设置列的宽度
        return sheet;
    }

    //导入excel
    @RequestMapping("/import")
    public String importExcel(@RequestParam("file") MultipartFile file,Model model,HttpSession session) {
        //固定格式  参数file 为 文件上传的input的name

        try {
            InputStream inputStream = null;
            inputStream = file.getInputStream();
            BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());

            HSSFSheet sheet = workbook.getSheetAt(0);

            int lastRowNum = sheet.getLastRowNum();
            TrainingOrder order = null;
            HSSFRow row = null;

            System.out.println("lastRowNum"+lastRowNum);
            //从第四行开始读取数据
            List<TrainingOrder> list = new ArrayList<>();
            for (int i = 3; i <= lastRowNum; i++) {
                order = new TrainingOrder();
                row = sheet.getRow(i);
                if(StringUtils.isEmpty(row.getCell(0).getStringCellValue()) && StringUtils.isEmpty(row.getCell(1).getStringCellValue()) && StringUtils.isEmpty(row.getCell(4).getStringCellValue())){
                    break;//如果姓名实训基地和性别都为空则判断以下是空行无数据，停止写入
                }
                //第一列
                order.setTrainingOrderName(row.getCell(0).getStringCellValue());
                //第二列 连起来写 直接给ds对象赋值
                order.setTrainingOrderTrainingBase(row.getCell(1).getStringCellValue());
                //第三列
                order.setTrainingOrderIDNumber(row.getCell(2).getStringCellValue());
                //第四列
                order.setTrainingOrderTel(row.getCell(3).getStringCellValue());
                //第五列
                order.setTrainingOrderSex(row.getCell(4).getStringCellValue());
                //第六列
                order.setTrainingOrderSchool(row.getCell(5).getStringCellValue());
                //第七列
                order.setTrainingOrderEducation(row.getCell(6).getStringCellValue());
                //第八列
                order.setTrainingOrderPayDate(row.getCell(7).getDateCellValue());
                //第九列
                order.setTrainingOrderPayType(row.getCell(8).getStringCellValue());
                //第十列
                order.setTrainingOrderSchoolSubsidy(row.getCell(9).getNumericCellValue());
                //第十一列
                order.setTrainingOrderSubsidyState(row.getCell(10).getStringCellValue());
                //第十二列
                order.setTrainingOrderLiveFee(row.getCell(11).getNumericCellValue());
                //第十三列
                order.setTrainingOrderLiveFeeCase(row.getCell(12).getNumericCellValue());
                //第十四列
                order.setTrainingOrderAgencyFee(row.getCell(13).getNumericCellValue());
                //第十五列
                order.setTrainingOrderDiscountType(row.getCell(14).getStringCellValue());
                //第十六列
                /*User m = userService.findByNameWitdM(row.getCell(15).getStringCellValue());*/
                User user = (User)session.getAttribute("user");//自己导入自己的表格，直接拿登录的user
                order.setUserChannelId(user.getUid());
                //第十七列
                User m = userService.findByNameWitdM(row.getCell(16).getStringCellValue());
                order.setUserConsultId(m.getUid());
                //第十八列
                order.setTrainingOrderRemarks(row.getCell(17).getStringCellValue());
                //添加一个对象,循环,直到结束
                list.add(order);
            }
            if (trainingOrderService.addTrainingOrderList(list)){
                MessageUtil.returnInfoPage("2","/training/trainingOrderList","导入成功",model);
                return "message";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        MessageUtil.returnInfoPage("3", "/training/trainingOrderList", "导入失败！请检查", model);
        return "message";
    }


}
